using System;
using System.Data;
using System.Data.Common;
using Fadd.Data.Mappings;

namespace Fadd.Data.ConnectionHelpers
{
    /// <summary>
    /// Connection helper specialized for PostgreSQL database.
    /// </summary>
    public class PostgreSqlHelper : IConnectionHelper
    {
        private readonly string _connectionString;

        /// <summary>
        /// Initializes a new instance of the <see cref="PostgreSqlHelper"/> class.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        public PostgreSqlHelper(string connectionString)
        {
            Check.Require(connectionString, "connectionString");

            _connectionString = connectionString;
        }

        /// <summary>
        /// Connection string
        /// </summary>
        /// <value></value>
        public string ConnectionString
        {
            get { return _connectionString; }
        }

        /// <summary>
        /// Create a new connection to the database.
        /// </summary>
        /// <returns>An open database connection.</returns>
        /// <exception cref="DataLayerException">If connection fails.</exception>
        public virtual IDbConnection CreateConnection()
        {
            //todo: Load npgsql assembly and use activator.createinstance,
            // or overload this method to create it directly (faster).
            //Activator.CreateInstance()
            return null;
        }

        public string ParameterPrefix
        {
            get { return "@"; }
        }

        /// <summary>
		/// Try to get next sequence value.
		/// </summary>
		/// <param name="command">A command to perform actions with, we pass commands since they might be in a transaction.</param>
		/// <param name="sequenceName">Name of the sequence/generator.</param>
		/// <returns>A generated value.</returns>
		/// <exception cref="DataLayerException">If something failed </exception>
        public object GetNextSequenceValue(IDbCommand command, string sequenceName)
        {
            try
            {
				command.CommandText = "SELECT nextval('" + sequenceName + "')";
				return command.ExecuteScalar();
            }
            catch (DbException err)
            {
                throw new DataLayerException("Failed to fetch next value for sequence '" + sequenceName + "'.", err);
            }
        }


		/// <summary>
		/// Gets the latest auto incremented value from the database.
		/// </summary>
		/// <param name="command">A command to perform actions with, we pass commands since they might be in a transaction..</param>
		/// <param name="columnName">Name of the column that the auto incremented value is for.</param>
		/// <returns>auto incremented value</returns>
		/// <exception cref="DataLayerException">If something failed </exception>
		/// <exception cref="NotSupportedException">Not supported by PostgreSQL</exception>
		public object GetAutoIncrementValue(IDbCommand command, string columnName)
        {
            throw new NotSupportedException("Not supported by PostgreSQL");
        }

        /// <summary>
        /// Modifies the SQL statement to include paging.
        /// </summary>
        /// <param name="mapping">Mapping used by certain implementations (but not by this one).</param>
        /// <param name="sqlStatement">SQL statement generated by the DAL.</param>
        /// <param name="page">0 based index</param>
        /// <param name="pageSize">number of entries per page</param>
        /// <returns>SQL statement with paging</returns>
        /// <example>
        /// 	<code>
        /// helper.Paging("SELECT * FROM users", 5, 10); // --&gt; "SELECT * FROM users LIMIT 10 OFFSET 50"
        /// </code>
        /// </example>
        public string Paging(IMapping mapping, string sqlStatement, int page, int pageSize)
        {
            if (page == 0)
                return sqlStatement + " LIMIT " + pageSize;

            return sqlStatement + " LIMIT " + pageSize + " OFFSET " + pageSize * page;
        }
    }
}
